﻿using System;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Collections;


/// <summary>
/// Summary description for db
/// </summary>
public class db
{
    MySql.Data.MySqlClient.MySqlConnection connection;
    public db()
    {
        connection = new MySqlConnection(GetConnectionString());
    }
    
    public int validUser(string userName, string password)
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "select id from logins where username='"+userName+"' and password='"+password+"'";
        connection.Open();
        int id = Convert.ToInt32(command.ExecuteScalar());
        connection.Close();
        if (id == 0)
        {
            return -1;
        }
        else
        {
            return id;
        }

        
    }
    public int removeDestination(int id)
    {
        //add in code to delete the image
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "DELETE FROM destination WHERE id = '" + id + "'";
        connection.Open();
        int result = command.ExecuteNonQuery();
        connection.Close();
        return result;
    }
    public ArrayList getCountries()
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT Name FROM countries";
        connection.Open();
        MySqlDataReader read = command.ExecuteReader();
        ArrayList countries = new ArrayList();
        while (read.Read())
        {
            countries.Add(read[0].ToString());
        }
        connection.Close();
        return countries;
    }
    public ArrayList getDestinations()
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT id, Name, Country, Description, Image, Price FROM destination";
        connection.Open();
        MySqlDataReader read = command.ExecuteReader();
        ArrayList myDestination = new ArrayList();

        while (read.Read())
        {
            
            destinations myDest = new destinations();
            myDest.Id = Convert.ToInt32(read[0]);
            myDest.Name = Convert.ToString(read[1]);
            myDest.Country = Convert.ToString(read[2]);
            myDest.Description = Convert.ToString(read[3]);
            myDest.Image = Convert.ToString(read[4]);
            myDest.Price = Convert.ToInt32(read[5]);
            myDestination.Add(myDest);
        }
        connection.Close();
        return myDestination;
    }
    public ArrayList searchDestinations(string terms)
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT id, Name, Country, Description, Image, Price FROM destination";
        connection.Open();
        MySqlDataReader read = command.ExecuteReader();
        ArrayList myDestination = new ArrayList();

        while (read.Read())
        {

            destinations myDest = new destinations();
            myDest.Id = Convert.ToInt32(read[0]);
            myDest.Name = Convert.ToString(read[1]);
            myDest.Country = Convert.ToString(read[2]);
            myDest.Description = Convert.ToString(read[3]);
            myDest.Image = Convert.ToString(read[4]);
            myDest.Price = Convert.ToInt32(read[5]);
            myDestination.Add(myDest);
        }
        connection.Close();
        return myDestination;
    }
    public int getUnreadMessage()
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT COUNT(id) from inquiries where checked='0'";
        connection.Open();
        int unreadMessages = Convert.ToInt32(command.ExecuteScalar());
        connection.Close();
        return unreadMessages;
    }
    public string getDestinationName(int id)
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT Name from destination where id='" + id + "'";
        connection.Open();
        string name = (string)command.ExecuteScalar();
        connection.Close();

        return name;
    }
    public string getUserName(int id)
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT name from logins where id='"+id + "'";
        connection.Open();
        string name= (string)command.ExecuteScalar();
        connection.Close();

        return name;
    }
    public int addDestination(string name, string country, string description, string image, int price)
    {
        MySqlCommand command = connection.CreateCommand();
        name = MySqlHelper.EscapeString(name);
        description = MySqlHelper.EscapeString(description);
        command.CommandText = "INSERT INTO destination (`id`, `Name`, `Country`, `Description`, `Image`, `Price`) VALUES (NULL, '"+name+"', '"+country+"', '"+description+"', '"+image+"', '"+price.ToString()+"')";//"INSERT INTO destination ('Name', 'Country', 'Description', 'Image', 'Price') VALUES ('"+  name+"','" + country + "','" + description + "','" + image + "'," + price +")";
        connection.Open();
        int result = command.ExecuteNonQuery();
        connection.Close();
        return result;
    }
    public string getTourName(string id)
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "select name from destination where id='" + id + "'";
        connection.Open();
        string name = (string)command.ExecuteScalar();
        connection.Close();

        return name;
    }
    public ArrayList getInquiries()
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT `id`,`destinationID`,`name`,`surname`,`email`,`comments`,`checked`, `requestTime` FROM `inquiries`";
        connection.Open();
        MySqlDataReader read = command.ExecuteReader();
        ArrayList inquiries = new ArrayList();
        while (read.Read())
        {
            inquiry newInquiry = new inquiry();
            newInquiry.SubmitTime = Convert.ToInt64(read[7]);
            newInquiry.Id = Convert.ToInt32(read[0]);
            newInquiry.Destination = Convert.ToInt32(read[1]);
            newInquiry.Name = read[2].ToString();
            newInquiry.Surname = read[3].ToString();
            newInquiry.Email = read[4].ToString();
            newInquiry.Comments = read[5].ToString();
            if (Convert.ToInt32(read[6]) == 0)
            {
                newInquiry.Responded = false;
            }
            else
            {
                newInquiry.Responded = true;
            }
            inquiries.Add(newInquiry); 
        }
        connection.Close();
        return inquiries;
    }
    public int addInquiryEntry(string tourId, string firstName, string lastName, string email, string comments)
    {
        firstName = MySqlHelper.EscapeString(firstName);
        lastName = MySqlHelper.EscapeString(lastName);
        email = MySqlHelper.EscapeString(email);
        comments = MySqlHelper.EscapeString(comments);
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "insert into inquiries values(null," + tourId + ",'" + firstName + "','" + lastName + "','" + email + "','" + comments + "', 0, " + DateTime.Now.Ticks + ")";
        connection.Open();
        int result = command.ExecuteNonQuery();
        connection.Close();
        return result;
    }
    public int markAsRead(int id, int isRead)
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "UPDATE `inquiries` SET `checked`="+isRead+" WHERE `id`="+id;
        connection.Open();
        int result = command.ExecuteNonQuery();
        connection.Close();
        return result;
    }
    public int addCountry(string country, string pic)
    {
        country = MySqlHelper.EscapeString(country);
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "INSERT INTO `countries` values(null, '"+country+"','"+pic+"')";
        connection.Open();
        int result = command.ExecuteNonQuery();
        connection.Close();
        return result;
    }
    private string GetConnectionString()
    {
        string connStr = String.Format("server={0};user id={1}; password={2};" +
          "database=cs490; pooling=false", "bai.bounceme.net",
          "root", "purdue");

        return connStr;
    }
    public ArrayList getLatestNews()
    {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT Id, Name, Country, Description FROM destination order by ID desc";
        connection.Open();
        MySqlDataReader read = command.ExecuteReader();
        ArrayList myDestination = new ArrayList();

        while (read.Read())
        {

            destinations myDest = new destinations();
            myDest.Id = Convert.ToInt32(read[0]);
            myDest.Name = Convert.ToString(read[1]);
            myDest.Country = Convert.ToString(read[2]);
            if (Convert.ToString(read[3]).Length> 200)
            {
                myDest.Description = Convert.ToString(read[3]).Substring(0, 200);
            }
            else
            {
                myDest.Description = Convert.ToString(read[3]);
            }
            myDestination.Add(myDest);
        }
        connection.Close();
        return myDestination;
    }
}